package com.example.denglu.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class ExcelUtil {

    /**
     * 后缀
     */
    private static final String SUFFIX = ".xlsx";
    /**
     * 导出模板
     */
  public   static void DownloadTemplate(HttpServletResponse response,String fileName ) throws IOException {
        try {
            Resource resource = new ClassPathResource("templates/"+fileName+SUFFIX);
            File file = resource.getFile();
//            String fileName = resource.getFilename();
            InputStream inputStream = new FileInputStream(file);
            //强制下载不打开
            response.setContentType("application/force-download");
            OutputStream out = response.getOutputStream();
            //使用URLEncoder来防止文件名乱码或者读取错误
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName+SUFFIX, "UTF-8"));
            int b = 0;
            byte[] buffer = new byte[1000000];
            while (b != -1) {
                b = inputStream.read(buffer);
                if (b != -1) {
                    out.write(buffer, 0, b);
                }
            }
            inputStream.close();
            out.close();
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     //     * 导出excel
     //     * @param fileName 导出的文件名
     //     * @param response 响应
     //     * @param request  请求
     //     * @param lists    导出的数据
     //     * @param <T>      导出的实体类泛型
     //     */
    public static <T> void writerExcel(String fileName, HttpServletResponse response, HttpServletRequest request, List<T> lists, Class<T> clazz) {
        String sheetName = fileName;
        try {
            String userAgent = request.getHeader("User-Agent");

            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                // 针对IE或者以IE为内核的浏览器：
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            } else {
                // 非IE浏览器的处理:
                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            }
            response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName + SUFFIX));
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Pragma", "no-cache");
            response.setDateHeader("Expires", -1);
        } catch (UnsupportedEncodingException e1) {
            log.error("导出excel未知编码异常", e1);
        }
        try {
            EasyExcel.write(response.getOutputStream(), clazz)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .sheet(sheetName)
                    .doWrite(lists);
        } catch (IOException e) {
           log.error("导出excel文件异常", e);
       }
    }
    /**
         * 导出带模板文件的excel
     * @param fileName     导出文件名
     * @param response     响应
     * @param request      请求
     * @param lists        导出的数据
     * @param <T>          导出的实体类泛型
     * @param clazz        导出的实体类
     * @param templatePath 模版文件路径
     */
    public static <T> void writerExcelByTemplate(String fileName, HttpServletResponse response, HttpServletRequest request, List<T> lists, Class<T> clazz, String templatePath) {

        String sheetName = fileName;
        try {
            String userAgent = request.getHeader("User-Agent");
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            }
            response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName + ".xlsx"));
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Pragma", "no-cache");
            response.setDateHeader("Expires", -1);
        } catch (UnsupportedEncodingException e1) {
            log.error("导出excel未知编码异常", e1);
        }
        try {
            EasyExcel.write(response.getOutputStream(), clazz)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .withTemplate(templatePath)
                    .sheet(0, sheetName)
                    .doWrite(lists);
        } catch (IOException e) {
            log.error("导出excel文件异常", e);
        }
    }

    /**
     * 导出模板信息（自定义表头）
     * @param fileName  导出文件名
     * @param response  响应
     * @param request   请求
     * @param heads     表头信息
     */
    public static void writerExcelWithHead(String fileName, HttpServletResponse response, HttpServletRequest request, List<String> heads) {
        String sheetName = fileName;
        try {
            String userAgent = request.getHeader("User-Agent");
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            }
            response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName + ".xlsx"));
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Pragma", "no-cache");
            response.setDateHeader("Expires", -1);
        } catch (UnsupportedEncodingException e1) {
            log.error("导出excel未知编码异常", e1);
        }
        try {
            EasyExcel.write(response.getOutputStream(), null)
//                    .head(head(heads))
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .sheet(sheetName)
                    .doWrite(new ArrayList());
        } catch (IOException e) {
            log.error("导出excel文件异常", e);
        }
    }


    /**
 * 导入导出excel
 */
    /**
     * 读取 Excel(多个 sheet)
     *
     * @param excel 文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);

        if (reader == null) {
            return null;
        }

        for (Sheet sheet : reader.getSheets()) {
            if (rowModel != null) {
                sheet.setClazz(rowModel.getClass());
            }
            reader.read(sheet);
        }

        return excelListener.getDataList();
    }


    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel 文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @param sheetNo sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) {
        return readExcel(excel, rowModel, sheetNo, 1);
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel 文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @param sheetNo sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);

        if (reader == null) {
            return null;
        }

        reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));

        return excelListener.getDataList();
    }

    /**
     * 返回 ExcelReader
     *
     * @param excel 需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     */
    private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) {
        String filename = excel.getOriginalFilename();

        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            throw new RuntimeException("文件格式错误！");
        }
        InputStream inputStream;

        try {
            inputStream = new BufferedInputStream(excel.getInputStream());

            return new ExcelReader(inputStream, null, excelListener, false);
        } catch (IOException e) {
            e.printStackTrace();
        }

        return null;
    }






}














//package com.example.demo.util.excel;
//
//        import com.alibaba.excel.EasyExcel;
//        import com.alibaba.excel.ExcelReader;
//        import com.alibaba.excel.read.metadata.ReadSheet;
//        import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
//        import lombok.extern.slf4j.Slf4j;
//        import org.springframework.web.multipart.MultipartFile;
//
//        import javax.servlet.http.HttpServletRequest;
//        import javax.servlet.http.HttpServletResponse;
//        import java.io.BufferedInputStream;
//        import java.io.IOException;
//        import java.io.InputStream;
//        import java.io.UnsupportedEncodingException;
//        import java.nio.charset.StandardCharsets;
//        import java.util.*;
//
///**
// * 导入导出excel
// */
//@Slf4j
//public class EasyExcelUtil {
//
//    /**
//     * 根据自定义对象读取excel
//     * @param <T>   实体类泛型
//     * @param excel 文件流
//     * @param clazz 实体类
//     * @return 导入的数据集合
//     */
//    public static <T> ArrayList<T> readerExcel(MultipartFile excel, Class<T> clazz) throws Exception {
//        if (excel.isEmpty()) {
//            throw new Exception("请选择excel文件");
//        }
//        String fileName = excel.getOriginalFilename();
//        log.info("Excel文件解析：文件名 = " + fileName);
//        if (fileName == null || (!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase().endsWith(".xlsx"))) {
//            throw new Exception("文件格式错误");
//        }
//        try(InputStream fileStream = new BufferedInputStream(excel.getInputStream())) {
//            GeneralExcelListener<T> excelListener = new GeneralExcelListener<>();
//            EasyExcel.read(fileStream, clazz, excelListener)
//                    .autoTrim(true)
//                    .sheet()
//                    .doRead();
//            return excelListener.getDatas();
//        } catch (Exception e) {
//            log.error("导入失败, 请检查导入数据的准确性", e);
//            throw new Exception("导入失败, 请检查导入数据的准确性");
//        }
//    }
//
//    /**
//     * 根据自定义对象读取excel并指定读取起始行
//     * @param <T>       实体类泛型
//     * @param excel     文件流
//     * @param rowNum    从第几行开始读
//     * @param clazz     实体类
//     * @return 导入的数据集合
//     */
//    public static <T> ArrayList<T> readExcelWithRowNum(MultipartFile excel, Integer rowNum, Class<T> clazz) throws Exception {
//        if (excel.isEmpty()) {
//            throw new Exception("请选择excel文件");
//        }
//        String fileName = excel.getOriginalFilename();
//        log.info("Excel文件解析：文件名 = " + fileName);
//        if (fileName == null || (!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase().endsWith(".xlsx"))) {
//            throw new Exception("文件格式错误");
//        }
//        try(InputStream fileStream = new BufferedInputStream(excel.getInputStream())) {
//            GeneralExcelListener<T> excelListener = new GeneralExcelListener<>();
//            EasyExcel.read(fileStream, clazz, excelListener)
//                    .autoTrim(true)
//                    .sheet()
//                    .headRowNumber(rowNum)
//                    .doRead();
//            return excelListener.getDatas();
//        } catch (Exception e) {
//            log.error("导入失败, 请检查导入数据的准确性", e);
//            throw new Exception("导入失败, 请检查导入数据的准确性");
//        }
//    }
//
//    /**
//     * 根据自定义对象读取excel并指定sheet列表读取
//     * sheet列表数据格式必须一样
//     * @param excel     excel文件
//     * @param sheetNums sheet集合
//     * @param clazz     实体类
//     * @param <T>       实体类泛型
//     * @return 导入的数据集合
//     */
//    public static <T> ArrayList<T> readExcelWithSheets(MultipartFile excel, List<Integer> sheetNums, Class<T> clazz) throws Exception {
//        if (excel.isEmpty()) {
//            throw new Exception("请选择excel文件");
//        }
//        String fileName = excel.getOriginalFilename();
//        log.info("Excel文件解析：文件名 = " + fileName);
//        if (fileName == null || (!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase().endsWith(".xlsx"))) {
//            throw new Exception("文件格式错误");
//        }
//        try(InputStream fileStream = new BufferedInputStream(excel.getInputStream())) {
//            GeneralExcelListener<T> excelListener = new GeneralExcelListener<>();
//            ExcelReader excelReader = EasyExcel.read(fileStream,clazz,excelListener).autoTrim(true).build();
//            List<ReadSheet> readSheets = new ArrayList<>();
//            for (Integer sheetNum : sheetNums) {
//                ReadSheet readSheet = EasyExcel.readSheet(sheetNum).build();
//                readSheets.add(readSheet);
//            }
//            excelReader.read(readSheets);
//            excelReader.finish();
//            return excelListener.getDatas();
//        } catch (Exception e) {
//            log.error("导入失败, 请检查导入数据的准确性", e);
//            throw new Exception("导入失败, 请检查导入数据的准确性");
//        }
//    }
//
//    /**
//     * 根据自定义对象读取excel并自定义sheets读取
//     * sheet列表数据格式必须一样
//     * @param excel excel文件
//     * @param sheets 自定义sheet
//     * @param clazz 实体类
//     * @param <T>   实体类泛型
//     * @return 导入的数据集合
//     */
//    public static <T> ArrayList<T> readExcelWithCustomSheet(MultipartFile excel, List<ReadSheet> sheets, Class<T> clazz) throws Exception {
//        if (excel.isEmpty()) {
//            throw new Exception("请选择excel文件");
//        }
//        String fileName = excel.getOriginalFilename();
//        log.info("Excel文件解析：文件名 = " + fileName);
//        if (fileName == null || (!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase().endsWith(".xlsx"))) {
//            throw new Exception("文件格式错误");
//        }
//        if (sheets == null){
//            throw new Exception("请指定sheet");
//        }
//        try(InputStream fileStream = new BufferedInputStream(excel.getInputStream())) {
//            GeneralExcelListener<T> excelListener = new GeneralExcelListener<>();
//            ExcelReader excelReader = EasyExcel.read(fileStream,clazz,excelListener).autoTrim(true).build();
//            excelReader.read(sheets);
//            excelReader.finish();
//            return excelListener.getDatas();
//        } catch (Exception e) {
//            log.error("导入失败, 请检查导入数据的准确性", e);
//            throw new Exception("导入失败, 请检查导入数据的准确性");
//        }
//    }
//
//
//    /**
//     * 导出excel
//     * @param fileName 导出的文件名
//     * @param response 响应
//     * @param request  请求
//     * @param lists    导出的数据
//     * @param <T>      导出的实体类泛型
//     */
//    public static <T> void writerExcel(String fileName, HttpServletResponse response, HttpServletRequest request, List<T> lists, Class<T> clazz) {
//        String sheetName = fileName;
//        try {
//            String userAgent = request.getHeader("User-Agent");
//
//            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
//                // 针对IE或者以IE为内核的浏览器：
//                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
//            } else {
//                // 非IE浏览器的处理:
//                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
//            }
//            response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName + ".xlsx"));
//            response.setHeader("Cache-Control", "no-cache");
//            response.setHeader("Pragma", "no-cache");
//            response.setDateHeader("Expires", -1);
//        } catch (UnsupportedEncodingException e1) {
//            log.error("导出excel未知编码异常", e1);
//        }
//        try {
//            EasyExcel.write(response.getOutputStream(), clazz)
//                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
//                    .sheet(sheetName)
//                    .doWrite(lists);
//        } catch (IOException e) {
//            log.error("导出excel文件异常", e);
//        }
//    }
//
//    /**
//     * 导出带模板文件的excel
//     * @param fileName     导出文件名
//     * @param response     响应
//     * @param request      请求
//     * @param lists        导出的数据
//     * @param <T>          导出的实体类泛型
//     * @param clazz        导出的实体类
//     * @param templatePath 模版文件路径
//     */
//    public static <T> void writerExcelByTemplate(String fileName, HttpServletResponse response, HttpServletRequest request, List<T> lists, Class<T> clazz, String templatePath) {
//
//        String sheetName = fileName;
//        try {
//            String userAgent = request.getHeader("User-Agent");
//            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
//                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
//            } else {
//                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
//            }
//            response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName + ".xlsx"));
//            response.setHeader("Cache-Control", "no-cache");
//            response.setHeader("Pragma", "no-cache");
//            response.setDateHeader("Expires", -1);
//        } catch (UnsupportedEncodingException e1) {
//            log.error("导出excel未知编码异常", e1);
//        }
//        try {
//            EasyExcel.write(response.getOutputStream(), clazz)
//                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
//                    .withTemplate(templatePath)
//                    .sheet(0, sheetName)
//                    .doWrite(lists);
//        } catch (IOException e) {
//            log.error("导出excel文件异常", e);
//        }
//    }
//
//    /**
//     * 导出模板信息（自定义表头）
//     * @param fileName  导出文件名
//     * @param response  响应
//     * @param request   请求
//     * @param heads     表头信息
//     */
//    public static void writerExcelWithHead(String fileName, HttpServletResponse response, HttpServletRequest request, List<String> heads) {
//        String sheetName = fileName;
//        try {
//            String userAgent = request.getHeader("User-Agent");
//            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
//                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
//            } else {
//                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
//            }
//            response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName + ".xlsx"));
//            response.setHeader("Cache-Control", "no-cache");
//            response.setHeader("Pragma", "no-cache");
//            response.setDateHeader("Expires", -1);
//        } catch (UnsupportedEncodingException e1) {
//            log.error("导出excel未知编码异常", e1);
//        }
//        try {
//            EasyExcel.write(response.getOutputStream(), null)
//                    .head(head(heads))
//                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
//                    .sheet(sheetName)
//                    .doWrite(new ArrayList());
//        } catch (IOException e) {
//            log.error("导出excel文件异常", e);
//        }
//    }
//
//    /**
//     * 获取表头
//     * @param strings 表头信息
//     * @return 表头
//     */
//    private static List<List<String>> head(List<String> strings) {
//        List<List<String>> list = new ArrayList<>();
//        for (String string : strings) {
//            List<String> head = new ArrayList<>();
//            head.add(string);
//            list.add(head);
//        }
//        return list;
//    }

//}


